package com.bounds.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.bounds.dao.PersonDao;
import com.bounds.entity.Pager;
import com.bounds.entity.Person;

public class PersonDaoImpl extends BaseDaoImpl<Person> implements PersonDao {

	@Override
	public List<Person> qryPersons(Person person) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM person WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(person != null){
			if(person.getId() != 0){
				sql.append(" AND ID =? ");
				params.add(person.getId());
			}
			if(StringUtils.isNotBlank(person.getName())){
				sql.append(" AND NAME = ? ");
				params.add(person.getName());
			}
		}
		final List<Person> list = new ArrayList<Person>();
		this.getJdbcTemplate().query(sql.toString(), params.toArray(), new RowCallbackHandler() {		
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Person person = new Person();
				setPerson(rs,person);
				list.add(person);
			}
		});
		return list;
	}
	private void setPerson(ResultSet rs,Person person) throws SQLException{
		person.setId(rs.getInt("id"));
		person.setName(rs.getString("name"));
		person.setAdmin(rs.getInt("admin"));
		person.setLevel(rs.getInt("level"));
		person.setUsername(rs.getString("username"));
		person.setSupport(rs.getString("support"));
		person.setPassword(rs.getString("password"));
		person.setManagerId(rs.getInt("manager_id"));
		person.setBase(rs.getInt("base"));
	}
	@Override
	public Pager<Person> qryPersonsForPager(Person person, int pageNo,
			int pageSize) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM person WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(person != null){
			if(person.getId() != 0){
				sql.append(" AND id =? ");
				params.add(person.getId());
			}
			if(StringUtils.isNotBlank(person.getName())){
				sql.append(" AND name LIKE ? ");
				params.add("%"+person.getName()+"%");
			}
		}

		this.initPager();
		this.setPageNo(pageNo);
		this.setPageSize(pageSize);	
		int rowCount = this.getRowCount(sql.toString(), params.toArray());
		if(rowCount>0){		
			final List<Person> list = new ArrayList<Person>();
			this.getJdbcTemplate().query(getExecuteSQL(sql.toString()), params.toArray(), new RowCallbackHandler() {		
				@Override
				public void processRow(ResultSet rs) throws SQLException {
					Person person = new Person();
					setPerson(rs,person);
					list.add(person);
				}
			});
			this.setData(list);
		}
		return this.getPager();
	}

	@Override
	public int addPerson(Person person) {
		String sql = "insert into person(name,username,password,level,admin,base) values(?,?,?,?,?,?)";
		Object[] object = new Object[]{
				person.getName(), 
				person.getUsername(),
				encrypt(person.getPassword()),
				person.getLevel(),
				person.getAdmin(),
				person.getBase()};
		return this.getJdbcTemplate().update(sql,object);
	}

	@Override
	public int deletePerson(int id) {
		return this.getJdbcTemplate().update("delete from person where id="+id);
	}

	private String encrypt(String password){
		//encrypt password
		String cryPassord = "123456";
		return cryPassord;
	}
	@Override
	public Person getPerson(Person person) {
		if(person == null){
			return null;
		}
		
		StringBuffer sql = new StringBuffer("SELECT * FROM person WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(person.getId() != 0){
			sql.append(" AND id = ?");
			params.add(person.getId());
		}
		if(StringUtils.isNotBlank(person.getPassword())){
			sql.append(" AND password = ? ");
			params.add(person.getPassword());
		}
		if(StringUtils.isNotBlank(person.getName())){
			sql.append(" AND  username = ?  ");
			params.add(person.getName().trim());
			//params.add(person.getName().trim());
		}
		final List<Person> list = new ArrayList<Person>();
		this.getJdbcTemplate().query(sql.toString(), params.toArray(), new RowCallbackHandler() {			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Person person = new Person();
				setPerson(rs,person);
				list.add(person);				
			}
		});
		if(list.isEmpty()){
			return null;
		}else{			
			return list.get(0);
		}
	}
	
	@Override
	public boolean updatePerson(Person person) {
		String sql = "UPDATE person SET name=?,username=?,password=?,level=?,admin=?,base=? where id=? ";
		Object[] params = {
				person.getName(),
				person.getUsername(),
				person.getPassword(),
				person.getLevel(),
				person.getAdmin(),
				person.getBase(),
				person.getId()
		};		
		return this.getJdbcTemplate().update(sql,params)>0;
	}
	
	@Override
	public boolean insertBatch(final List<Person> persons) {
		String sql = "insert into person(name,username,password,level,admin,base) values(?,?,?,?,?,?)";
		try {
			this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
				@Override
				public int getBatchSize() {
					return persons.size();
				}
				@Override
				public void setValues(PreparedStatement ps, int index)throws SQLException {
					ps.setString(1, persons.get(index).getName());
					ps.setString(2, persons.get(index).getUsername());
					ps.setString(3, persons.get(index).getPassword());
					ps.setInt(4, persons.get(index).getLevel());
					ps.setInt(5, persons.get(index).getAdmin());
					ps.setInt(6, persons.get(index).getBase());
					//ps.setInt(7, persons.get(index).getId());
					
				}			
			});
			return true;
		} catch (DataAccessException e) {
			e.printStackTrace();
			return false;
		}
	}
	@Override
	public Person getPersonById(int id) {
		if(id==0){
			return null;
		}
		
		StringBuffer sql = new StringBuffer("SELECT * FROM person WHERE id= "+id);
		final List<Person> list = new ArrayList<Person>();
		this.getJdbcTemplate().query(sql.toString(), new RowCallbackHandler() {			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Person person = new Person();
				setPerson(rs,person);
				list.add(person);				
			}
		});
		if(list.isEmpty()){
			return null;
		}else{			
			return list.get(0);
		}
	} 
	
}
